﻿
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_CF_AddUpdateFile_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_CF_AddUpdateFile_V2];
GO

CREATE PROCEDURE [dbo].[sproc_CF_AddUpdateFile_V2]
    @catalogId  int,
    @name       nvarchar(255),
    @version    nvarchar(64),
    @size       int,
    @vPath      nvarchar(512),
    @invalid    bit,
    @creator    nvarchar(50),
    @createNew  bit = 1,
    @fileId     int output
AS
BEGIN
SET NOCOUNT ON;

IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

DECLARE @timeNow datetime;
SET @timeNow = GETDATE();

IF ( @createNew = 1 )
BEGIN
    -- Create a new file
    INSERT INTO dbo.UDS_CF_File (CatalogId, [Name], [Version], [Size], VPath, Creator, Invalid, TimeCreated, LastModified)
        VALUES(@catalogId, @name, @version, @size, @vPath, @creator, @invalid, @timeNow, @timeNow);
    SET @fileId = @@IDENTITY;
END
ELSE
BEGIN
    -- Update the existing file
    UPDATE dbo.UDS_CF_File
        SET
            CatalogId   = @catalogId,
            [Name]      = @name,
            [Version]   = @version,
            [Size]      = @size,
            VPath       = @vPath,
            Creator     = @creator,
            Invalid     = @invalid,
            LastModified    = @timeNow
        WHERE FileID = @fileID;

    IF ( @@ROWCOUNT = 0 )
    BEGIN
        RAISERROR(N'Failed to update the specified file "%d" since it does not exist.', 16, 1, @fileId);
    END
END

END
GO
